package cc.mrbird.febs.accounting.service.impl;

import cc.mrbird.febs.accounting.dto.business.ModuleDTO;
import cc.mrbird.febs.accounting.dto.detailCost.DetailIncomeDTO;
import cc.mrbird.febs.accounting.dto.detailCost.ProjectDTO;
import cc.mrbird.febs.business.entity.Business;
import cc.mrbird.febs.accounting.entity.DetailCost;
import cc.mrbird.febs.accounting.entity.Project;
import cc.mrbird.febs.accounting.entity.StandardSubExtra;
import cc.mrbird.febs.accounting.mapper.DetailCostMapper;
import cc.mrbird.febs.accounting.mapper.StandardSubExtraMapper;
import cc.mrbird.febs.accounting.service.*;
import cc.mrbird.febs.business.entity.BusinessModule;
import cc.mrbird.febs.business.mapper.BusinessMapper;
import cc.mrbird.febs.business.mapper.BusinessModuleMapper;
import cc.mrbird.febs.statistics.service.impl.DataExportServiceImpl;
import cc.mrbird.febs.utils.*;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class ExportServiceImpl implements IExportService {

    @Resource
    private DetailCostMapper detailCostMapper;
    @Resource
    private StandardSubExtraMapper standardSubExtraMapper;
    @Resource
    private BusinessModuleMapper businessModuleMapper;
    @Resource
    private BusinessMapper businessMapper;
    @Resource
    private DataExportServiceImpl dataExportServiceImpl;

    @Override
    public void queryIncomeForExcel(List<Project> projects, String year, List<String> yearMonths, XSSFWorkbook workbook, String choice ) {
//        查明细项目额外表
        List<StandardSubExtra> detailCodes = standardSubExtraMapper.getSpecialType(new ArrayList<>(Arrays.asList("4", "5")));
//      Map根据code对应类型
        Map<String, Integer> codeToTypeMap = new HashMap<>();
        detailCodes.forEach(e->codeToTypeMap.put(e.getCode(), e.getType().intValue()));
//      查detailCost表
        LambdaQueryWrapper<DetailCost> queryWrapper2 = new LambdaQueryWrapper<>();
        queryWrapper2.eq(DetailCost::getIsDeleted, 0);
        queryWrapper2.and(e -> e.eq(DetailCost::getType, 4).or().eq(DetailCost::getType, 5));
        List<DetailCost> detailCosts = detailCostMapper.selectList(queryWrapper2);
//      数据保存在projectDTOS中，第一个for: 初始化
        List<ProjectDTO> projectDTOS = new ArrayList<>();
        for (Project project : projects) {
            Long id = project.getId();
            ProjectDTO projectDTO = new ProjectDTO();
            projectDTO.setProjectId(id);
            projectDTO.setProjectName(project.getProjectName());

            List<DetailIncomeDTO> detailIncomeDTOS = new ArrayList<>();
            DetailIncomeDTO detailIncomeDTO;
            for (StandardSubExtra detailCost : detailCodes) {
                if ( Objects.equals(detailCost.getProjectId(), id) && detailCost.getType() == 5 ){
                    detailIncomeDTO = new DetailIncomeDTO();
                    detailIncomeDTO.setDetailSubjectCode(detailCost.getCode());
                    detailIncomeDTO.setDetailSubjectName(detailCost.getName());
                    detailIncomeDTOS.add(detailIncomeDTO);
                }
            }
//            detailIncomeDTO = new DetailIncomeDTO();
//            detailIncomeDTO.setDetailSubjectName("自有收入统计");
//            detailIncomeDTO.setDetailSubjectCode(CodeConstant.selfIncomeCode);
//            detailIncomeDTOS.add(detailIncomeDTO);

            for (StandardSubExtra detailCode : detailCodes) {
                if ( Objects.equals(detailCode.getProjectId(), id) && detailCode.getType() == 4 ){
                    detailIncomeDTO = new DetailIncomeDTO();
                    detailIncomeDTO.setDetailSubjectCode(detailCode.getCode());
                    detailIncomeDTO.setDetailSubjectName(detailCode.getName());
                    detailIncomeDTOS.add(detailIncomeDTO);
                }
            }
//            detailIncomeDTO = new DetailIncomeDTO();
//            detailIncomeDTO.setDetailSubjectName("第三方收入统计");
//            detailIncomeDTO.setDetailSubjectCode(CodeConstant.thirdPartCode);
//            detailIncomeDTOS.add(detailIncomeDTO);
            detailIncomeDTO = new DetailIncomeDTO();
            detailIncomeDTO.setDetailSubjectName("合计");
            detailIncomeDTO.setDetailSubjectCode(CodeConstant.projectIncomeCode);
            detailIncomeDTOS.add(detailIncomeDTO);

            projectDTO.setDetailIncomeDTOS(detailIncomeDTOS);
            projectDTOS.add(projectDTO);
        }


        Map<String, BigDecimal> incomeMap = new HashMap<>();
        Map<String, Integer> estimateMap = new HashMap<>();
        detailCosts.forEach(e -> {
            String key = e.getProjectId().toString().concat("-")
                    .concat(e.getDetailSubjectCode()).concat("-").concat(e.getYearMonth());
            incomeMap.put(key, e.getCost());
            estimateMap.put(key, e.getEstimate());
        });
        List<String> specialCodes = new ArrayList<>();
//        specialCodes.add(CodeConstant.thirdPartCode);
//        specialCodes.add(CodeConstant.selfIncomeCode);
        specialCodes.add(CodeConstant.projectIncomeCode);
        for (ProjectDTO projectDTO : projectDTOS) {
//            double thirdFlagIncome = 0.0;
//            double thirdOtherIncome = 0.0;
//            double selfFlagIncome = 0.0;
//            double selfOtherIncome = 0.0;
            BigDecimal allFlagIncome = new BigDecimal("0");
            BigDecimal allOtherIncome = new BigDecimal("0");
            Set<Integer> thirdMonth = new HashSet<>();
            Set<Integer> selfMonth = new HashSet<>();
            Set<Integer> allMonth = new HashSet<>();
            Map<String, DetailIncomeDTO> codeToDtoMap = new HashMap<>();
            projectDTO.getDetailIncomeDTOS().forEach(e->codeToDtoMap.put(e.getDetailSubjectCode(), e));

            for (DetailIncomeDTO detailIncomeDTO : projectDTO.getDetailIncomeDTOS()) {
                if(!specialCodes.contains(detailIncomeDTO.getDetailSubjectCode())){
                    BigDecimal result1 = new BigDecimal("0");
                    BigDecimal result2 = new BigDecimal("0");
                    List<String> months = new ArrayList<>();
                    for (String yearMonth : yearMonths) {
                        String key = projectDTO.getProjectId()+"-"+detailIncomeDTO.getDetailSubjectCode()+"-"+yearMonth;
                        BigDecimal cost = incomeMap.getOrDefault(key, BigDecimal.ZERO);
                        Integer estimate = estimateMap.getOrDefault(key, 3);
                        if( estimate == 0){
                            result1 = cost.add(result1);
                            months.add(Integer.parseInt(yearMonth.split("-")[1])+"");
//                            if(codeToTypeMap.getOrDefault(detailIncomeDTO.getDetailSubjectCode(),1) == 4) {
//                                thirdMonth.add(Integer.parseInt(yearMonth.split("-")[1]));
//                            }
//                            if(codeToTypeMap.getOrDefault(detailIncomeDTO.getDetailSubjectCode(),1) == 5) {
//                                selfMonth.add(Integer.parseInt(yearMonth.split("-")[1]));
//                            }
                            allMonth.add(Integer.parseInt(yearMonth.split("-")[1]));
                        }
                        if(estimate == 1){
                            result2 = cost.add(result2);
                        }
                    }
                    detailIncomeDTO.setMonths(CodeUtils.monthStyle(StringUtils.join(months, ",")));
                    BigDecimal result3 = result1.add(result2);
                    detailIncomeDTO.setFlagIncome(result2);
                    detailIncomeDTO.setOtherIncome(result1);
                    detailIncomeDTO.setAllIncome(result3);
//                    if(codeToTypeMap.getOrDefault(detailIncomeDTO.getDetailSubjectCode(), 1) == 4){
//                        thirdFlagIncome = ArithUtil.add(result2, thirdFlagIncome);
//                        thirdOtherIncome = ArithUtil.add(result1, thirdFlagIncome);
//                    }
//                    if(codeToTypeMap.getOrDefault(detailIncomeDTO.getDetailSubjectCode(), 1) == 5){
//                        selfFlagIncome = ArithUtil.add(result2, selfFlagIncome);
//                        selfOtherIncome = ArithUtil.add(result1, selfOtherIncome);
//                    }
                    allFlagIncome = result2.add(allFlagIncome);
                    allOtherIncome = result1.add(allOtherIncome);
                }
            }
//            List<Integer> thirdList = new ArrayList<>(thirdMonth);
//            Collections.sort(thirdList);
//            List<Integer> selfList = new ArrayList<>(selfMonth);
//            Collections.sort(selfList);
            List<Integer> allList = new ArrayList<>(allMonth);
            Collections.sort(allList);

//            DetailIncomeDTO third = codeToDtoMap.getOrDefault(CodeConstant.thirdPartCode, null);
//            third.setFlagIncome(thirdFlagIncome);
//            third.setOtherIncome(thirdOtherIncome);
//            third.setAllIncome(ArithUtil.add(thirdFlagIncome, thirdOtherIncome));
//            third.setMonths(CodeUtils.monthStyle(StringUtils.join(thirdList, ",")));
//            DetailIncomeDTO self = codeToDtoMap.getOrDefault(CodeConstant.selfIncomeCode, null);
//            self.setFlagIncome(selfFlagIncome);
//            self.setOtherIncome(selfOtherIncome);
//            self.setAllIncome(ArithUtil.add(selfFlagIncome, selfOtherIncome));
//            self.setMonths(CodeUtils.monthStyle(StringUtils.join(selfList, ",")));
            DetailIncomeDTO all = codeToDtoMap.getOrDefault(CodeConstant.projectIncomeCode, null);
            all.setFlagIncome(allFlagIncome);
            all.setOtherIncome(allOtherIncome);
            all.setAllIncome(allFlagIncome.add(allOtherIncome));
            all.setMonths(CodeUtils.monthStyle(StringUtils.join(allList, ",")));
        }

        for (ProjectDTO projectDTO : projectDTOS) {
            projectDTO.getDetailIncomeDTOS().removeIf(e->e.getAllIncome().compareTo(BigDecimal.ZERO)==0 && e.getFlagIncome().compareTo(BigDecimal.ZERO)==0
                    && e.getOtherIncome().compareTo(BigDecimal.ZERO)==0 && !specialCodes.contains(e.getDetailSubjectCode()));
        }
        XSSFSheet sheet = workbook.createSheet(year+"年拓天项目未开票明细汇总表");   // 创建一个表
        setMainTitle(sheet, workbook, year, yearMonths, "项目名称", choice);
        setDetailData(sheet, workbook,  projectDTOS, choice);
        if(choice.equals("2")) {
            ExportUtil.deleteColumn(sheet,1);
        }
    }

    public void setMainTitle(XSSFSheet sheet, XSSFWorkbook workbook, String year, List<String> yearMonths, String name, String choice) {
        // 第一行
        XSSFRow row = sheet.createRow(0);     // 创建一行
        sheet.createRow(1);
        XSSFCellStyle titleStyle = workbook.createCellStyle();
        StyleMap.addStyle(workbook, titleStyle, 14, "微软雅黑", 0,2, 0);
        XSSFCell cell = row.createCell(0);  // 在row行columnIndex列创建单元格
        cell.setCellValue(year + "年拓天项目未开票明细汇总表"); // 添加单元格内容
        cell.setCellStyle(titleStyle);
        row.setHeightInPoints(23);
        // 合并单元格
        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 1, 0, 4+Integer.parseInt(choice));
        sheet.addMergedRegion(cellRangeAddress);

        //        统计时间time
        Date day=new Date();
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        String time = df.format(day);
//        统计期间aMonths
        List<Integer> aMonths = new ArrayList<>();
        for (String yearMonth : yearMonths) {
            aMonths.add(Integer.parseInt(yearMonth.split("-")[1]));
        }
        String aMonth = CodeUtils.monthStyle(StringUtils.join(aMonths, ","));

        row = sheet.createRow(2);
        row.setHeightInPoints(19);
        XSSFCellStyle secondRowStyle1 = StyleMap.getIncomeSecondRow1(workbook);
        XSSFCellStyle secondRowStyle2 = StyleMap.getIncomeSecondRow2(workbook);
        XSSFCellStyle secondRowStyle3 = StyleMap.getIncomeSecondRow3(workbook);

        XSSFCell cell1 = row.createCell(3);
        cell1.setCellValue("统计期间"); // 添加单元格内容
        cell1.setCellStyle(secondRowStyle1);

        XSSFCell cell2 = row.createCell(4);
        cell2.setCellValue(aMonth);  // 添加单元格内容
        cell2.setCellStyle(secondRowStyle2);

        XSSFCell cell3 = row.createCell(5);
        cell3.setCellValue("统计时间");  // 添加单元格内容
        cell3.setCellStyle(secondRowStyle1);

        XSSFCell cell4 = row.createCell(6);
        cell4.setCellValue(time);      // 添加单元格内容
        cell4.setCellStyle(secondRowStyle2);

        XSSFCell cell5 = row.createCell(7);
        cell5.setCellValue("单位：元");      // 添加单元格内容
        cell5.setCellStyle(secondRowStyle3);

        dataExportServiceImpl.setColWidth(sheet, 7);

        row = sheet.createRow(3);
        row.setHeightInPoints(22);
        XSSFCellStyle style = workbook.createCellStyle();
        XSSFColor xssfColor = new XSSFColor(new java.awt.Color(221, 217, 196));  // 设置颜色
        style.setAlignment(HorizontalAlignment.CENTER);
        style = StyleMap.MainRowStyle(workbook, style, xssfColor, 10, "微软雅黑", 0);
        String []arr = new String[]{name,"业务明细","总收入","开票收入","未开票收入", "未开票占比", "已开票月份", "已开票未到款"};
        for( int i=0; i<8; i++ ) {
            XSSFCell cell6 = row.createCell(i);
            cell6.setCellValue(arr[i]);      // 添加单元格内容
            cell6.setCellStyle(style);
        }
    }

    public void setDetailData(XSSFSheet sheet, XSSFWorkbook workbook, List<ProjectDTO> projectDTOS, String choice) {
        int startRow = 4;
        XSSFCellStyle mainStyle = workbook.createCellStyle();
        StyleMap.addStyle(workbook, mainStyle, 10, "微软雅黑", 0, 2, 1);
        XSSFCellStyle titleStyle = workbook.createCellStyle();
        XSSFCellStyle subTitleStyle = workbook.createCellStyle();
        XSSFColor xssfColor = new XSSFColor(new java.awt.Color(220, 230, 241));  // 设置颜色
        titleStyle.setAlignment(HorizontalAlignment.LEFT);        // 设置水平靠左
        StyleMap.MainRowStyle(workbook, titleStyle, xssfColor, 10, "微软雅黑", 1);
        StyleMap.MainRowStyle(workbook, subTitleStyle, xssfColor, 10, "微软雅黑", 1);
        subTitleStyle.setAlignment(HorizontalAlignment.LEFT);
        BigDecimal allIncome = new BigDecimal("0");
        BigDecimal flagIncome = new BigDecimal("0");
        BigDecimal otherIncome = new BigDecimal("0");
        for ( int i=0; i < projectDTOS.size(); i++ ) {
            String projectName = projectDTOS.get(i).getProjectName();
            List<DetailIncomeDTO> detailIncomeDTO  =  projectDTOS.get(i).getDetailIncomeDTOS();
            for (int j=0; j < detailIncomeDTO.size(); j++ ) {
                if(choice.equals("2") && j != detailIncomeDTO.size()-1) {
                    continue;
                }
                String subjectName = detailIncomeDTO.get(j).getDetailSubjectName();
                XSSFRow row;
                if (choice.equals("2")) {
                    row = sheet.createRow((short) (startRow));
                } else{
                    row = sheet.createRow((short) (startRow + j));
                }
//                row.setHeightInPoints(18);
                String rate;
                if (detailIncomeDTO.get(j).getAllIncome().compareTo(BigDecimal.ZERO) != 0) {
                    BigDecimal tmpRate = detailIncomeDTO.get(j).getFlagIncome().multiply(new BigDecimal("100")).divide(detailIncomeDTO.get(j).getAllIncome(), RoundingMode.CEILING);
                    rate = String.format("%.2f", tmpRate) + "%";
                } else {
                    rate = "#VALUE!";
                }
                String []data = new String[]{projectName, subjectName, String.valueOf(detailIncomeDTO.get(j).getAllIncome()),
                        String.valueOf(detailIncomeDTO.get(j).getOtherIncome()), String.valueOf(detailIncomeDTO.get(j).getFlagIncome()),
                        rate, detailIncomeDTO.get(j).getMonths(), ""};
                for( int k=0; k<8; k++ ) {
                    XSSFCell cell6 = row.createCell(k);
                    cell6.setCellValue(data[k]);      // 添加单元格内容
                    if(k == 0) {
                        cell6.setCellStyle(titleStyle);
                    } else if (k == 1) {
                        cell6.setCellStyle(subTitleStyle);
                    } else {
                        cell6.setCellStyle(mainStyle);
                    }
                }
                allIncome = detailIncomeDTO.get(j).getAllIncome().add(allIncome) ;
                flagIncome = detailIncomeDTO.get(j).getFlagIncome().add(flagIncome);
                otherIncome = detailIncomeDTO.get(j).getOtherIncome().add(otherIncome);
            }
            if(detailIncomeDTO.size() > 1 && choice.equals("1")) {
                CellRangeAddress cellRangeMain = new CellRangeAddress(startRow, startRow + detailIncomeDTO.size() - 1, 0, 0);
                sheet.addMergedRegion(cellRangeMain);
            }
            if (choice.equals("1")){
                startRow += detailIncomeDTO.size();
            } else {
                startRow += 1;
            }
        }
        XSSFCellStyle style = workbook.createCellStyle();
        XSSFColor xssfColor2 = new XSSFColor(new java.awt.Color(221, 217, 196));  // 设置颜色
        style.setAlignment(HorizontalAlignment.CENTER);
        StyleMap.MainRowStyle(workbook, style, xssfColor2, 10, "微软雅黑", 0);
        XSSFRow row = sheet.createRow((short) (startRow));
        String rate;
        if (allIncome.compareTo(BigDecimal.ZERO) != 0) {
            BigDecimal tmpRate = flagIncome.multiply(new BigDecimal("100")).divide(allIncome, RoundingMode.CEILING);
            rate = String.format("%.2f", tmpRate) + "%";
        } else {
            rate = "#VALUE!";
        }
        String []total = new String[]{"合计", "", String.format("%.2f", allIncome), String.format("%.2f", otherIncome),
                String.format("%.2f", flagIncome), rate, "", ""};
        for( int k=0; k<8; k++ ) {
            XSSFCell cell = row.createCell(k);
            cell.setCellValue(total[k]);      // 添加单元格内容
            cell.setCellStyle(style);
        }
    }

    @Override
    public void exportBusinessIncome(List<Project> projects, List<String> projectIds, String year, List<String> yearMonths, XSSFWorkbook workbook){
        List<BusinessModule> modules = businessModuleMapper.selectList(new LambdaQueryWrapper<>());
        LambdaQueryWrapper<Business> queryWrapper = new LambdaQueryWrapper<>();

        queryWrapper.ne(Business::getType, 6);
        List<Business> businessesForMap = businessMapper.selectList(queryWrapper);
        List<Business> businesses = businessesForMap.stream().filter(e -> e.getFatherId().equals(0L)).collect(Collectors.toList());
        Map<String, Long> map8 = new HashMap<>();
        for (Business business : businesses) {
            map8.put(business.getCode(), business.getModuleId());
        }
        List<ModuleDTO> moduleDTOS = new ArrayList<>();

        LambdaQueryWrapper<DetailCost> queryWrapper2 = new LambdaQueryWrapper<>();
        queryWrapper2.eq(DetailCost::getIsDeleted, 0);
        queryWrapper2.in(DetailCost::getProjectId, projectIds);
        queryWrapper2.in(DetailCost::getYearMonth, yearMonths);
        queryWrapper2.and(e -> e.eq(DetailCost::getType, 4).or().eq(DetailCost::getType, 5));
        List<DetailCost> detailCosts = detailCostMapper.selectList(queryWrapper2);
        Map<String, String> map1 = TreeUtils.getFatherNode(businessesForMap);
        Map<String, BigDecimal> map2 = new HashMap<>();   // estimate == 0
        Map<String, BigDecimal> map3 = new HashMap<>();   // estimate == 1
        Map<String, Set<String>> map4 = new HashMap<>();   // businessCode->month
        Map<Long, Set<String>> map7 = new HashMap<>();    // moduleId-> month

        Map<Long, BigDecimal> map5 = new HashMap<>();     // moduleCode->cost, estimate == 0
        Map<Long, BigDecimal> map6 = new HashMap<>();     // moduleCode->cost, estimate == 1
        Set<String> businessName = new HashSet<>();
        Map<String, String> map9 = new HashMap<>();     // 将重复的code转换成唯一的code
        Map<String, String> map10 = new HashMap<>();   // 名字对code
        for (BusinessModule module : modules) {
            ModuleDTO moduleDTO = new ModuleDTO();
            moduleDTO.setModuleId(module.getId());
            moduleDTO.setModuleName(module.getModuleName());
            List<Business> businesses1 = new ArrayList<>();
            for (Business business : businesses) {
                if(business.getModuleId().equals(module.getId())){
                    if (business.getFatherId().equals(0L)) {
                        if(!businessName.contains(business.getName())) {
                            businessName.add(business.getName());
                            businesses1.add(business);
                            map10.put(business.getName(), business.getCode());
                        } else {
                            map9.put(business.getCode(), map10.get(business.getName()));
                        }
                    }
                }
            }
            moduleDTO.setBusinesses(businesses1);
            moduleDTOS.add(moduleDTO);
        }
        for (DetailCost detailCost : detailCosts) {
            String key;
            if(map9.containsKey(map1.get(detailCost.getDetailSubjectCode())))
                key = map9.get(map1.get(detailCost.getDetailSubjectCode()));
            else
                key = map1.get(detailCost.getDetailSubjectCode());
            if(detailCost.getEstimate()==0){
                map2.put(key, detailCost.getCost().add(map2.getOrDefault(key,BigDecimal.ZERO)));
//                String key2 = detailCost.getDetailSubjectCode();
                String mon = Integer.parseInt(detailCost.getYearMonth().split("-")[1])+"";
                Set<String> temp = map4.getOrDefault(key, new HashSet<>());
                temp.add(mon);
                map4.put(key, temp);

                Set<String> t = map7.getOrDefault(map8.get(key), new HashSet<>());
                t.add(mon);
                map7.put(map8.get(key), t);
            } else{
                map3.put(key, detailCost.getCost().add(map3.getOrDefault(key,BigDecimal.ZERO)));
            }
        }
        for (ModuleDTO moduleDTO : moduleDTOS) {
            for (Business business : moduleDTO.getBusinesses()) {
                map5.put(moduleDTO.getModuleId(), map2.getOrDefault(business.getCode(), BigDecimal.ZERO).add(map5.getOrDefault(moduleDTO.getModuleId(), BigDecimal.ZERO)));
                map6.put(moduleDTO.getModuleId(), map3.getOrDefault(business.getCode(), BigDecimal.ZERO).add(map6.getOrDefault(moduleDTO.getModuleId(),BigDecimal.ZERO)));
//                map7.put(module.getId(), map4.get(business.getCode()));
            }
        }
        XSSFSheet sheet = workbook.createSheet(year+"年拓天项目未开票明细汇总表");   // 创建一个表
        setMainTitle(sheet, workbook, year, yearMonths, "业务名称", "1");
        // 删除金额为零的业务和模块(等价于查询条件中未包含业务)
        for (ModuleDTO moduleDTO : moduleDTOS) {
            moduleDTO.getBusinesses().removeIf(e -> {
                BigDecimal cost = map3.getOrDefault(e.getCode(), BigDecimal.ZERO)
                        .add(map2.getOrDefault(e.getCode(), BigDecimal.ZERO));    // estimate = 1
                return cost.equals(BigDecimal.ZERO);
            });
        }
        moduleDTOS.removeIf(e -> e.getBusinesses().size() == 0);
        setBusinessIncomeForExcel(sheet, workbook, moduleDTOS, map2, map3, map4, map5, map6, map7);
    }

    // map2: estimate == 0;
    // map3: estimate == 1;
    // map4: businessCode->month;
    // map5: moduleCode->cost, estimate == 0;
    // map6: moduleCode->cost, estimate == 1;
    // map7: moduleId-> month;
    public void setBusinessIncomeForExcel(XSSFSheet sheet, XSSFWorkbook workbook, List<ModuleDTO> moduleDTOS, Map<String, BigDecimal> map2, Map<String, BigDecimal> map3,
                                          Map<String, Set<String>> map4, Map<Long, BigDecimal> map5, Map<Long, BigDecimal> map6, Map<Long, Set<String>> map7) {
        int startRow = 4;
        XSSFCellStyle mainStyle = workbook.createCellStyle();
        StyleMap.addStyle(workbook, mainStyle, 10, "微软雅黑", 0, 2, 1);
        XSSFCellStyle titleStyle = workbook.createCellStyle();
        XSSFCellStyle subTitleStyle = workbook.createCellStyle();
        XSSFColor xssfColor = new XSSFColor(new java.awt.Color(220, 230, 241));  // 设置颜色
        titleStyle.setAlignment(HorizontalAlignment.LEFT);        // 设置水平靠左
        StyleMap.MainRowStyle(workbook, titleStyle, xssfColor, 10, "微软雅黑", 1);
        StyleMap.MainRowStyle(workbook, subTitleStyle, xssfColor, 10, "微软雅黑", 1);
        subTitleStyle.setAlignment(HorizontalAlignment.LEFT);
        BigDecimal allIncome = new BigDecimal("0");
        BigDecimal flagIncome = new BigDecimal("0"); // 未开票，estimate = 1
        BigDecimal otherIncome = new BigDecimal("0");
        for (int i = 0; i < moduleDTOS.size(); i++) {
            String moduleName = moduleDTOS.get(i).getModuleName();
            List<Business> businesses = moduleDTOS.get(i).getBusinesses();
            // allIncome1, flagIncome1, otherIncome1对应的是业务模块的合计收入项
            BigDecimal allIncome1 = new BigDecimal("0");
            BigDecimal flagIncome1 = new BigDecimal("0"); // 未开票，estimate = 1
            BigDecimal otherIncome1 = new BigDecimal("0");
            for (int j = 0; j < businesses.size(); j++) {
                String businessName = businesses.get(j).getName();
                // flagIncome2, otherIncome2, allIncome2对应的是单项业务明细
                BigDecimal flagIncome2 = map3.getOrDefault(businesses.get(j).getCode(), BigDecimal.ZERO);    // estimate = 1
                BigDecimal otherIncome2 = map2.getOrDefault(businesses.get(j).getCode(), BigDecimal.ZERO);    // estimate = 0
                BigDecimal allIncome2 = flagIncome2.add(otherIncome2);
                String rate;
                if (allIncome2.compareTo(BigDecimal.ZERO) != 0) {
                    BigDecimal tmpRate = flagIncome2.multiply(new BigDecimal("100")).divide(allIncome2, 2);
                    rate = String.format("%.2f", tmpRate) + "%";
                } else {
                    rate = "#VALUE!";
                }
                List<String> m = new ArrayList<>(map4.getOrDefault(businesses.get(j).getCode(), new HashSet<>()));
                List<Integer> mon = m.stream().map(Integer::parseInt).sorted().collect(Collectors.toList());
                String []data = new String[]{moduleName, businessName, String.valueOf(allIncome2),
                        String.valueOf(otherIncome2), String.valueOf(flagIncome2), rate,
                        CodeUtils.monthStyle(StringUtils.join(mon, ",")), ""};
                XSSFRow row;
                row = sheet.createRow((short) (startRow + j));
                for( int k=0; k<8; k++ ) {
                    XSSFCell cell6 = row.createCell(k);
                    cell6.setCellValue(data[k]);      // 添加单元格内容
                    if(k == 0) {
                        cell6.setCellStyle(titleStyle);
                    } else if (k == 1) {
                        cell6.setCellStyle(subTitleStyle);
                    } else {
                        cell6.setCellStyle(mainStyle);
                    }
                }
                allIncome1 = allIncome1.add(allIncome2) ;
                flagIncome1 = flagIncome1.add(flagIncome2);
                otherIncome1 = otherIncome1.add(otherIncome2);
                allIncome = allIncome.add(allIncome2) ;
                flagIncome = flagIncome.add(flagIncome2);
                otherIncome = otherIncome.add(otherIncome2);
            }
            List<String> m = new ArrayList<>(map7.getOrDefault(moduleDTOS.get(i).getModuleId(), new HashSet<>()));
            List<Integer> mon = m.stream().map(Integer::parseInt).sorted().collect(Collectors.toList());
            String rate;
            if (allIncome1.compareTo(BigDecimal.ZERO) != 0) {
                BigDecimal tmpRate = flagIncome1.multiply(new BigDecimal("100")).divide(allIncome1, 2);
                rate = String.format("%.2f", tmpRate)+"%";
            } else {
                rate = "#VALUE!";
            }
            String []data = new String[]{moduleName, "合计", String.valueOf(allIncome1),
                    String.valueOf(otherIncome1), String.valueOf(flagIncome1), rate,
                    CodeUtils.monthStyle(StringUtils.join(mon, ",")), ""};
            XSSFRow row = sheet.createRow((short) (startRow + businesses.size()));
            for( int k=0; k<8; k++ ) {
                XSSFCell cell6 = row.createCell(k);
                cell6.setCellValue(data[k]);      // 添加单元格内容
                if(k == 0) {
                    cell6.setCellStyle(titleStyle);
                } else if (k == 1) {
                    cell6.setCellStyle(subTitleStyle);
                } else {
                    cell6.setCellStyle(mainStyle);
                }
            }
            if(businesses.size() > 0) {
                CellRangeAddress cellRangeMain = new CellRangeAddress(startRow, startRow + businesses.size(), 0, 0);
                sheet.addMergedRegion(cellRangeMain);
            }
            startRow += (businesses.size() + 1);
        }
        XSSFCellStyle style = workbook.createCellStyle();
        XSSFColor xssfColor2 = new XSSFColor(new java.awt.Color(221, 217, 196));  // 设置颜色
        style.setAlignment(HorizontalAlignment.CENTER);
        StyleMap.MainRowStyle(workbook, style, xssfColor2, 10, "微软雅黑", 0);
        XSSFRow row = sheet.createRow((short) (startRow));
        String rate;
        if (allIncome.compareTo(BigDecimal.ZERO) != 0) {
            BigDecimal tmpRate = flagIncome.multiply(new BigDecimal("100")).divide(allIncome, 2);
            rate = String.format("%.2f", tmpRate)+"%";
        } else {
            rate = "#VALUE!";
        }
        String []total = new String[]{"合计", "", String.format("%.2f", allIncome), String.format("%.2f", otherIncome),
                String.format("%.2f", flagIncome), rate, "", ""};
        for( int k=0; k<8; k++ ) {
            XSSFCell cell = row.createCell(k);
            cell.setCellValue(total[k]);      // 添加单元格内容
            cell.setCellStyle(style);
        }
    }

}